package study.itwill.team5.board;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import study.itwill.common.DBHandler;
import study.itwill.common.Util;

public class Board {
	/**
	 * 공지사항, 문의하기, 후기 작성을 위한 계시판
	 * 
	 * @param Board_seq
	 *            멤버 일련번호
	 * @param title
	 *            제목
	 * @param content
	 *            내용
	 * @param hit
	 *            조회수
	 * @param borad_category_seq
	 *            글 종류
	 * @return 작성 성공 true 리턴, 실패면 false
	 */

	// Logger 클래스의 인스턴스를 받아온다.
	static Logger logger = Logger.getLogger(Board.class);

	static final String TABLE = "Board";

	public int addBoard(String title, String content,
			int board_category_seq, int member_seq) {
		// -----------------------------------------
		// 메서드 시작
		// -----------------------------------------
		logger.debug("=============== method start >> addBoard");
		logger.debug("[PARAMS] title=" + title);
		logger.debug("[PARAMS] content=" + content);
		logger.debug("[PARAMS] board_category_seq=" + board_category_seq);
		logger.debug("[PARAMS] member_seq=" + member_seq);

		// 리턴할 값 선언
		int seq = -1;

		// SQL 인젝션 방어
		title = title.replace("'", "\'");
		content = content.replace("'", "\'");

		// 트랜젝션에 대한 판단이 잘 서지 않는다면, 무조건 하면 된다.
		DBHandler.beginTrans();

		// INSERT 처리를 위한 구현
		String sql = "INSERT INTO board(title, content,hit,reg_date,edit_date,"
				+ " board_category_seq, member_seq) VALUES ( '%s', '%s',0,'%s','%s', %d, %d)";

		
		boolean result = DBHandler.execSql(sql,  title, content,
				Util.getTime(), Util.getTime() , board_category_seq, member_seq);

		// -----------------------------------------
		// 결과처리
		// -----------------------------------------
		if (result) {

			DBHandler.commit();
			seq = DBHandler.getMax(TABLE, "seq", null);
			logger.debug("처리결과 >> " + seq + "번 게시글 등록 성공");
		} else {
			DBHandler.rollback();
			logger.error("처리결과 >> 게시글 추가 실패");
		}

		logger.debug("=============== method end >> addBoard");

		return seq;

	}

	public boolean editBoard(int seq,String title, String content, 
			int board_category_seq,int member_seq) {
		logger.debug("=============== method start >> editBoard"); 
		logger.debug("[PARAMS] seq=" + seq);
		logger.debug("[PARAMS] title=" + title);
		logger.debug("[PARAMS] content=" + content);
		logger.debug("[PARAMS] board_category_seq=" + board_category_seq);
		logger.debug("[PARAMS] member_seq=" + member_seq);
		
		// SQL 인젝션 방어
		title = title.replace("'", "\'");
		content = content.replace("'", "\'");

		boolean result = false;

		DBHandler.beginTrans();

		String sql = "UPDATE board SET title = '%s',content = '%s' "
				+ "edit_date = '%s', board_category_seq = %d, member_seq = %d  WHERE seq = %d;";

		result = DBHandler.execSql(sql, title, content, Util.getTime(),
				board_category_seq, member_seq, seq);

		if (result) {
			DBHandler.commit();
			logger.debug("처리결과 >> " + board_category_seq + "번 게시글 수정성공");
		} else {
			DBHandler.rollback();
			logger.error("처리결과 >> 게시글 수정 실패 ");
		}

		logger.debug("=============== method end >> editBoard");

		return result;
	}

	public boolean deleteBoard(int seq) {
		logger.debug("=============== method start >> deleteBoard");
		logger.debug("[PARAMS] seq=" + seq);

		boolean result = false;

		DBHandler.beginTrans();

		String sql = "DELETE FROM board WHERE seq = %d;";
		result = DBHandler.execSql(sql, seq);
		 
		if (result) {
			DBHandler.commit();
			logger.debug("처리결과 >> " + seq + "번 게시글 성공");
		} else {
			DBHandler.rollback();
			logger.error("처리결과 >> 게시글 삭제 실패 ");
		}

		logger.debug("=============== method end >> deleteBoard");

		return result;

	}

	public BoardItem viewBoard(int seq) {
		logger.debug("=============== method start >> viewBoard");
		logger.debug("[PARAMS] seq=" + seq);

		BoardItem item1 = null;
		
		String sql = "SELECT board.seq, board.member_seq, member.user_name, board.title, "
				+ "board.content, board.hit, board.board_category_seq, "
				+ "board_category.category_name, board.reg_date, board.edit_date "
				+ "FROM board INNER JOIN board_category ON "
				+ "board.board_category_seq = board_category.seq INNER JOIN member "
				+ "ON board.member_seq = member.seq WHERE board.seq = %d";

		ResultSet rs = DBHandler.getResult(sql, seq);

		if (rs != null) {
			// 데이터 조회 결과 있는 경우
			try {
				// 1) ResultSet의 모든 데이터를 변수로 추출
				rs.next();
				int seq1 = rs.getInt(1);
				int member_seq = rs.getInt(2);
				String user_name = rs.getString(3);
				String title = rs.getString(4);
				String content = rs.getString(5);
				int hit = rs.getInt(6);
				int board_category_seq = rs.getInt(7);
				String category_name = rs.getString(8);
				String reg_date = rs.getString(9);
				String edit_date = rs.getString(10);
				

				// 2) 추출된 값을 객체로 구성
				item1 = new BoardItem(seq1, member_seq, user_name, title, content, hit, board_category_seq, category_name, reg_date, edit_date);

			} catch (SQLException e) {
				logger.error(e);
			} finally {
				// 3) 데이터 조회에 대한 메모리 반납
				try {
					rs.close();
				} catch (SQLException e) {
				}
				rs = null;
			}
		}

		if (item1 == null) {
			logger.error("데이터 조회 실패");
		} else {
			logger.debug("데이터 조회 성공");
			logger.debug(item1.toString());
		}

		logger.debug("=============== method end >> viewBoard");

		return item1;

	}

	public ArrayList<BoardItem> listBoard(int choice,String search) {
		logger.debug("=============== method start >> listBoard");
		logger.debug("[PARAMS] choice=" + choice);
		logger.debug("[PARAMS] search=" + search);
		
		ArrayList<BoardItem> list = null;

		// 데이터 조회
		String sql = null;
		switch (choice) {
		case 0:
			sql = "SELECT board.seq, board.member_seq, member.user_name, board.title, "
					+ "board.content, board.hit, board.board_category_seq, "
					+ "board_category.category_name, board.reg_date, board.edit_date "
					+ "FROM board INNER JOIN board_category ON "
					+ "board.board_category_seq = board_category.seq INNER JOIN member "
					+ "ON board.member_seq = member.seq";
			
			break;
		case 1:
			sql = "SELECT board.seq, board.member_seq, member.user_name, board.title, "
					+ "board.content, board.hit, board.board_category_seq, "
					+ "board_category.category_name, board.reg_date, board.edit_date "
					+ "FROM board INNER JOIN board_category ON board_category_seq = board_category.seq "
					+ "INNER JOIN member ON member_seq = member.seq "
					+ "WHERE board_category_seq = " + search + " "
					+ "ORDER BY board.seq DESC;";
			        
			break;
		case 2:
			sql = "SELECT board.seq, board.member_seq, member.user_name, board.title, "
					+ "board.content, board.hit, board.board_category_seq, "
					+ "board_category.category_name, board.reg_date, board.edit_date "
					+ "FROM board INNER JOIN board_category ON "
					+ "board.board_category_seq = board_category.seq INNER JOIN member "
					+ "ON board.member_seq = member.seq" 
					+ "WHERE title LIKE '%%"
					+ search + "%%';";
			break;
		case 3:
			sql = "SELECT board.seq, board.member_seq, member.user_name, board.title, "
					+ "board.content, board.hit, board.board_category_seq, "
					+ "board_category.category_name, board.reg_date, board.edit_date "
					+ "FROM board INNER JOIN board_category ON "
					+ "board.board_category_seq = board_category.seq INNER JOIN member "
					+ "ON board.member_seq = member.seq"
					+ "WHERE content LIKE '%%"
					+ search + "%%';";
			break;
		case 4:
			sql = "SELECT board.seq, board.member_seq, member.user_name, board.title, "
					+ "board.content, board.hit, board.board_category_seq, "
					+ "board_category.category_name, board.reg_date, board.edit_date "
					+ "FROM board INNER JOIN board_category ON "
					+ "board.board_category_seq = board_category.seq INNER JOIN member "
					+ "ON board.member_seq = member.seq"
					+ "WHERE user_name LIKE '%%"
					+ search + "%%';";
			break;			
		}
		

		ResultSet rs = DBHandler.getResult(sql);

		if (rs != null) {
			// 데이터 조회 결과 있는 경우
			try {
				list = new ArrayList<BoardItem>();

				while (rs.next()) {

					// 1) ResultSet의 모든 데이터를 변수로 추출
					int seq = rs.getInt(1);
					int member_seq = rs.getInt(2);
					String user_name = rs.getString(3);
					String title = rs.getString(4);
					String content = rs.getString(5);
					int hit = rs.getInt(6);
					int board_category_seq = rs.getInt(7);
					String category_name = rs.getString(8);
					String reg_date = rs.getString(9);
					String edit_date = rs.getString(10);

					// 2) 추출된 값을 객체로 구성
					BoardItem item = new BoardItem(seq, member_seq, user_name, title, content, hit, board_category_seq, category_name, reg_date, edit_date);

					// 3) ArrayList에 추가
					list.add(item);
				}
			} catch (SQLException e) {
				logger.error(e);
			} finally {
				// 4) 데이터 조회에 대한 메모리 반납
				try {
					rs.close();
				} catch (SQLException e) {
				}
				rs = null;
			}
		}

		if (list == null) {
			logger.error("데이터 조회 실패");
		} else {
			logger.debug("데이터 조회 성공 >> " + list.size() + "건 조회됨");
		}

		logger.debug("=============== method end >> listBoard");

		return list;
	}
	/**
	 * listMember 게시물 검색시 search 값 = null일 때 전체검색을 위한 재정의
	 * @param choice - null일 경우 값을 받는 파라미터
	 * @return  - 검색된 목록을 ArrayList 형태로 리턴
	 */ 
	public ArrayList<BoardItem> listBoard(int choice) {
		// TODO Auto-generated method stub
		logger.debug("=============== method start >> listBoard(0)");
		logger.debug("=============== method end >> listBoard(0)");
		return listBoard(choice, null);
	}
	
}